# -*- coding: utf-8 -*-


import  wx
import  os
import  wx.wizard as wiz

#import  images
import  encodings.mbcs
import  codecs
import GenericTable
import wx.grid
import validator
from time import strftime, localtime  # gmtime, 
import wizdata
import random
from  db import *
import wizpages
import xport
from  util import *
from pyExcelerator import *


class MapGrid(wx.grid.Grid):
    def __init__(self, parent):
        wx.grid.Grid.__init__(self, parent, -1,size=(400,400))
        #self.file = file
        #xls = excel.excel(self.file)
        #xls.getData()
        data = ((u'选择映射','',''),
        (u'或创建映射','','' )
        )
        self.x = 0
        self.y = 0
        rowLabels = ('0','1','2','3','4','5','6','7','8')
        col = []
        data = []
        for x in range(20):
            x = '%s' %(x)
            y = ''
            col.append(x)
            data.append(y)
        colLabels = tuple(col) #(u'存储列名', u'映射字段名', u'当前列名' )
        tdata=[]
        tdata.append(tuple(data))
        data = tuple(tdata)
        """
        for x in rowLabels:
            print x,
        """
        
        
        self.tableBase = GenericTable.GenericTable(data, rowLabels, colLabels)        
        self.SetTable(self.tableBase)
        
        
    def update(self, parent, obj):
        #print strftime("%a, %d %b %Y %H:%M:%S +0000", gmtime())
        #(strftime("%H:%M:%S", gmtime())
        data = ((u'列','','' ),
        (u'长','',obj['uid'] ),
        (u'度','','' ),
        (u'不','','' ),
        (u'匹','','' ),
        (u'配','','' ))
        
        #tdb = db.db()
        sql = "select sum(value) as value, colname \
                from aid_items where employee_uid = '%s' group by colname" %(obj['uid'])
        #print sql
        result_set = db.execute(sql)
        #result_set = tdb.fetchall ()
        rowLabels =[]
        colLabels = []
        data = []
        if result_set:
            total = 0
            for x in result_set:
                oneline = []
                #print "%s:%s" %(x['colname'], x['value']),
                rowLabels.append(vcode(x.colname))
                oneline.append(x.value)
                total = total + x.value
                oneline.append('')
                oneline = tuple(oneline)
                data.append(oneline)
            oneline = []
            rowLabels.append(u'总计')
            oneline.append(total)
            oneline.append('')
            oneline = tuple(oneline)
            data.append(oneline)
                
            rowLabels = tuple(rowLabels)
            data = tuple(data)
            colLabels = ('','') #tuple(colLabels)
            rowLabels = tuple(rowLabels)

        else:
            data = ((u'空',''),(u'空',''))
            rowLabels = ('','')
            colLabels = ('','')
        self.tableBase.update(self, data, rowLabels, colLabels)
        self.SetTable(self.tableBase)
        self.ForceRefresh()

class IncomeInfo(wx.Panel):
    def __init__(self, parent):
        wx.Panel.__init__(self, parent)
        self.next = self.prev = None
        
        #title = wx.StaticText(self, -1, title)
        #title.SetFont(wx.Font(18, wx.SWISS, wx.NORMAL, wx.BOLD))

        #self.sizer = wx.BoxSizer(wx.VERTICAL)
        self.sizer = wx.GridBagSizer( hgap=5, vgap=5)
        self.SetSizer(self.sizer)

        
        self.sp = wx.SplitterWindow(self, size=(800,800))
        self.p1 = wx.Panel(self.sp, style=wx.SP_NOBORDER)
        
        
        self.p2 =  wx.Panel(self.sp, style=wx.SP_NOBORDER)
                
        self.p2.grid = MapGrid(self.p2)
        """
        self.p2.sizer = wx.BoxSizer(wx.VERTICAL)
        self.p2.SetSizer(self.sizer)
        """

        #self.p2.Fit()
        #self.sizer.Add(self.grid)
        #self.BuildRight()
        
        self.tree = TreeCtrl(self, self.p1,self.p2)
        """
        self.p1.sizer = wx.GridBagSizer(hgap=5, vgap=5)
        self.p1.SetSizer(self.p1.sizer)
        self.p1.Fit()
        """
        self.sp.Initialize(self.p1)
        #self.sp.Initialize(self.p2)
        #self.sp.SetMinimumPaneSize(10)
        self.sp.SplitVertically(self.p1, self.p2, 200)
        self.sp.SetMinimumPaneSize(160)
        self.sp.SetSize((600,600))
        #self.sizer.Add(title,pos=(0,0))
        
        """
        self.state = wx.TextCtrl(self, -1, "", size=(100, -1),validator=validator.CharValidator())
        self.state.SetEditable(False)
        self.sizer.Add(self.state,pos=(0,1))
        """
        self.tr = wx.Button(self, -1, u"清空数据")
        self.tr.Bind(wx.EVT_BUTTON, self.OnTruncate)
        self.sizer.Add(self.tr,pos=(0,1))

        self.ex = wx.Button(self, -1, u"导出数据")
        self.ex.Bind(wx.EVT_BUTTON, self.OnExport)
        self.sizer.Add(self.ex,pos=(0,2))


        self.sizer.Add(self.sp,pos=(1,0),span=(1,3))
        #self.sizer.Add(self.p2.grid, pos=(1,1) )
        self.Fit()
      

    def OnTruncate(self, event):
        
        dialog = wx.MessageDialog(None,  u'您确认要清空数据么?', u'清空数据确认', wx.OK|wx.CANCEL)
        result = dialog.ShowModal()
        dialog.Destroy()
        if result != wx.ID_OK:
            pass
        else:
            sql = "Truncate table aid_files"  # truncate the table for my sql
            db.execute(sql)
            #print sql
            sql = "Truncate table aid_items"
            db.execute(sql)
        
        event.Skip()
            
    def OnExport(self, event):
        #tdb = db.db()
        
        
        """
        summary by employee:
        """
        
        sql = """
        select emp.name as empname, sum(it.value)  as value, it.colname as colname 
        from aid_items it 
        inner join aid_employees emp on(emp.uid = it.employee_uid and emp.access = 1) 
        group by it.colname, emp.name order by emp.oid        
        """
        
        
        result_set = db.execute(sql)
        # = tdb.fetchall()
        data = []
        title=(u'姓名',u'条目',u'金额')
        data.append(title)
        lastname = ''
        end = 1
        bn = 0
        for oneline in result_set:
            end = end +1
            #print oneline
            #print oneline['empname'], oneline['value'], oneline['colname']
            row = []
            
            if lastname == oneline.empname:
                row.append('')
                bn = bn +1
            else:
                #bn = bn +1
                if end>2:
                    sum = ('',u'总计',Formula('SUM(C%s : C%s)'%(end-bn, end-1)))
                    data.append(sum)
                    end = end +1
                
                row.append(oneline.empname.decode('utf8'))
                lastname = oneline.empname
                bn = 1
            row.append(oneline.colname.decode('utf8'))
            #row.append(oneline.value)
            row.append(float(oneline.value))  #Decimal to float
            data.append(tuple(row))
            """
            row = []
            row.append('')
            row.append(u'总计')
            row.append('SUM(C%s : C%s)'%(end, end))
            data.append(tuple(row))
            """
            
        sum = ('',u'总计',Formula('SUM(C%s : C%s)'%(end-bn+1, end)))
        data.append(sum)
            
        data = tuple(data)
        sheet = u'工资单'
        date = strftime("%Y%m%d%H%M", localtime())
        filename = 'data%s.xls'%(date)
        x = xport.Xport(data, sheet, filename)
        os.startfile(filename)
        print "done"
        event.Skip()



class RightPanel(wx.Panel):
    def __init__(self, parent, id, pos, size, style):
        wx.Panel.__init__(self, parent, id, pos, size, style)
        

      
class MyTreeCtrl(wx.TreeCtrl):
    def __init__(self, parent, id, pos, size, style):
        wx.TreeCtrl.__init__(self, parent, id, pos, size, style)


    def OnCompareItems(self, item1, item2):
        t1 = self.GetItemText(item1)
        t2 = self.GetItemText(item2)
        if t1 < t2: return -1
        if t1 == t2: return 0
        return 1

class TreeCtrl:
    def __init__(self, parent, panel, p2):
        panel.Bind(wx.EVT_SIZE, self.OnSize)
        self.parent = parent
        self.panel = panel
        self.p2 = p2
        self.tree = MyTreeCtrl(panel, -1,wx.DefaultPosition, wx.DefaultSize, wx.TR_HAS_BUTTONS)
        self.root = self.tree.AddRoot(u"员工")
        self.tree.SetPyData(self.root, None)
        
        #tdb = db.db()
        result_set = db.execute("select uid, name \
                from aid_employees where access = 1 order by oid")
        #result_set = tdb.fetchall ()

        """
        for x in _treelist:
            #print x[0][0]
            child = self.tree.AppendItem(self.root, u"类型 %s" % x[0][0])
            #mypath = {'a':'b'}
            self.tree.SetPyData(child, x[0][0])
            for y in x[1]:
                k =  y.keys()[0]
                v =  y[k]
                child1 = self.tree.AppendItem(child, u"规则"+v)
                self.tree.SetPyData(child1, x[1])
            self.tree.Expand(child)
        """    
        #if result_set:
        for x in result_set:
            #print x['name']
            child = self.tree.AppendItem(self.root, vcode(x.name))  # data from database need to be docode
            self.tree.SetPyData(child, x)
            #self.tree.Expand(child)    
        self.tree.Expand(self.root)
        
        #self.BuildRight()
        #self.p2.ctrl = wx.TextCtrl(self.p2, -1, "xxx")
        
        self.panel.Bind(wx.EVT_TREE_ITEM_ACTIVATED, self.OnActivate, self.tree)
        self.panel.Bind(wx.EVT_TREE_SEL_CHANGED, self.OnSelChanged, self.tree)
                
    def OnSize(self, event):
        w,h = self.panel.GetClientSizeTuple()
        #print w,h
        self.tree.SetDimensions(0, 0, w, h)  
        event.Skip()
        
    def OnActivate(self, event):
        #print "OK -->"
        pass
        
    def changeState(self, state, val):
        data = wizdata.WizData()
        data.newmap = 0
        self.parent.newcb.SetValue(False)
        #if state == True:
        data.selmap = val            
        self.parent.state.SetValue(val) #.decode('utf8')            
        #else:
        #    data.selmap = ''
        #    self.parent.state.SetValue('')
        
    def OnSelChanged(self, event):
        data = wizdata.WizData()
        data.option = 1             # option for how to save
        self.item = event.GetItem()
        #print self.item
        count = self.tree.GetChildrenCount(self.item)
        val =  self.tree.GetItemText(self.item)
        #itemdata = self.tree.GetItemData(self.item)
        #print itemdata.GetId()
        #obj = self.tree.GetItemPyData(self.item)
        map_set = self.tree.GetPyData(self.item)

        if isinstance(map_set, dict):
            #print map_set
            pass

        if count == 0 and map_set != None :  # 可变节点
            #data.selmap = val
            #data.newmap = 0
            #self.parent.newcb.SetValue(False)
            self.p2.grid.update(self, map_set)  # pass data to it.
        else: # 不可变节点， 目前不存在
            """
            if self.parent.newcb.GetValue():
                data.selmap = u"新建映射"
                self.changeState(True, data.selmap)
            else:
                data.selmap = ''
            """
            pass
        #self.p2.ctrl.SetValue(val)
        #print self.tree.GetBoundingRect(self.item, True)
        #print data.selmap.encode('utf8')
        #self.parent.state.SetValue(data.selmap)
        #print "new:%s sel:%s"%(data.newmap, data.selmap.encode('utf8'))
        
        #update_session(map_set,0)
        event.Skip()
    
class TestFrame(wx.Frame):
    def __init__(self):
        wx.Frame.__init__(self, None, -1, u"查看", size=(800, 800),style = wx.DEFAULT_FRAME_STYLE) 
        #wizard = wiz.Wizard(self, -1, u"数据导入向导",style=wx.DEFAULT_DIALOG_STYLE|wx.RESIZE_BORDER)
        
        page1 = IncomeInfo(self)
        #wizard.FitToPage(page1)
        #wizard.SetPageSize(size=(800, 800))
        #wizard.RunWizard(page1)

def test():
    app = wx.PySimpleApp()
    TestFrame().Show()
    app.MainLoop() 


if __name__ == "__main__":
    test()
